package net.hn.hnms.biz.energy.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import net.hn.hnms.biz.energy.vo.WaterUseAnalysisVo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.poi.ss.formula.functions.T;

import java.util.List;

/**
 * @author 刘昊
 * @date 2024-03-01 11:04
 * 用水情况分析
 */
@Mapper
public interface WaterUseAnalysisMapper extends BaseMapper<T> {

    /**
     * 环比统计用水量
     *
     */
    @Select("<script>" +
            "select coalesce(((sum(water_consumption)::numeric) - (select sum(water_consumption)::numeric from water_settlement where mine_code in (#{mineCode}) and del_flag = '0' and to_char(meter_date ,'YYYYMM') = to_char((SELECT now()::timestamp - interval '1 month'),'YYYYMM'))) / (select sum(water_consumption)::numeric from water_settlement where mine_code in (#{mineCode}) and del_flag = '0' and to_char(meter_date ,'YYYYMM') = to_char((SELECT now()::timestamp - interval '1 month'),'YYYYMM')) * 100, 0) as month_on_month_change " +
            "from water_settlement " +
            "where mine_code in (#{mineCode}) " +
            "and del_flag = '0' " +
            "and to_char(meter_date,'YYYYMM') = to_char((SELECT now()::timestamp),'YYYYMM') " +
            "</script>")
    List<WaterUseAnalysisVo> getWaterAnalysisMonth(@Param("mineCode") String mineCode);

    /**
     * 同比统计用水量
     *
     */
    @Select("<script>" +
            "select coalesce(((sum(water_consumption)::numeric) - (select sum(water_consumption)::numeric from water_settlement where mine_code in (#{mineCode}) and del_flag = '0' and to_char(meter_date ,'YYYYMM') = to_char((SELECT now()::timestamp - interval '1 year'),'YYYYMM'))) / (select sum(water_consumption)::numeric from water_settlement where mine_code in (#{mineCode}) and del_flag = '0' and to_char(meter_date ,'YYYYMM') = to_char((SELECT now()::timestamp - interval '1 year'),'YYYYMM')) * 100, 0) as year_on_year_change " +
            "from water_settlement " +
            "where mine_code in (#{mineCode}) " +
            "and del_flag = '0' " +
            "and to_char(meter_date,'YYYY') = to_char((SELECT now()::timestamp),'YYYY') " +
            "</script>")
    List<WaterUseAnalysisVo> getWaterAnalysisYear(@Param("mineCode") String mineCode);

    List<WaterUseAnalysisVo> getYearOnYearChange(@Param("mineCode") String mineCode,@Param("companyCode") String companyCode);

    List<WaterUseAnalysisVo> getMonthOnMonthChange(@Param("mineCode") String mineCode,@Param("companyCode") String companyCode);

}
